package com.zhao.fishingguideserver.utils;

import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;

/**
 * 鱼类图片SQL生成工具
 */
@Component
@Slf4j
public class FishImageSqlGenerator {
    
    /**
     * 生成鱼类图片URL更新SQL
     * @param ossDomain OSS域名
     * @param folderPath 文件夹路径
     * @param fishNames 鱼类名称列表
     * @return SQL语句列表
     */
    public List<String> generateUpdateSql(String ossDomain, String folderPath, List<String> fishNames) {
        List<String> sqlList = new ArrayList<>();
        
        // 确保域名以/结尾
        if (!ossDomain.endsWith("/")) {
            ossDomain += "/";
        }
        
        // 如果文件夹路径为空，则不添加路径
        if (folderPath != null && !folderPath.trim().isEmpty()) {
            // 确保文件夹路径以/结尾
            if (!folderPath.endsWith("/")) {
                folderPath += "/";
            }
        } else {
            folderPath = "";
        }
        
        log.info("开始生成SQL，OSS域名: {}, 文件夹路径: {}, 鱼类数量: {}", ossDomain, folderPath, fishNames.size());
        
        for (String fishName : fishNames) {
            // 使用完整的学名，不清理特殊字符
            String fileName = fishName;
            
            // 构建图片URL，使用.webp扩展名
            String imageUrl = ossDomain + folderPath + fileName + ".webp";
            
            // 生成SQL语句
            String sql = String.format(
                "UPDATE freshwater_fish_china_1979 SET fish_img_url = '%s' WHERE species_name = '%s';",
                imageUrl, fishName
            );
            
            sqlList.add(sql);
            log.debug("生成SQL: {}", sql);
        }
        
        log.info("SQL生成完成，共生成 {} 条语句", sqlList.size());
        return sqlList;
    }
    
    /**
     * 生成完整的SQL脚本
     * @param ossDomain OSS域名
     * @param folderPath 文件夹路径
     * @param fishNames 鱼类名称列表
     * @return 完整的SQL脚本
     */
    public String generateCompleteSqlScript(String ossDomain, String folderPath, List<String> fishNames) {
        StringBuilder script = new StringBuilder();
        
        script.append("-- 鱼类图片批量更新SQL脚本\n");
        script.append("-- 生成时间: ").append(java.time.LocalDateTime.now()).append("\n");
        script.append("-- OSS域名: ").append(ossDomain).append("\n");
        script.append("-- 文件夹路径: ").append(folderPath).append("\n\n");
        
        script.append("-- 开始事务\n");
        script.append("START TRANSACTION;\n\n");
        
        List<String> sqlList = generateUpdateSql(ossDomain, folderPath, fishNames);
        
        for (String sql : sqlList) {
            script.append(sql).append("\n");
        }
        
        script.append("\n-- 提交事务\n");
        script.append("COMMIT;\n\n");
        
        script.append("-- 查询更新结果\n");
        script.append("SELECT species_name, fish_img_url \n");
        script.append("FROM freshwater_fish_china_1979 \n");
        script.append("WHERE fish_img_url IS NOT NULL \n");
        script.append("ORDER BY species_name;\n");
        
        return script.toString();
    }
    
    /**
     * 获取所有鱼类名称列表
     * @return 鱼类名称列表
     */
    public List<String> getAllFishNames() {
        return List.of(
            "鲤鱼", "鲫鱼", "草鱼", "青鱼", "鲢鱼", "鳙鱼", "鲶鱼", "黄颡鱼", "泥鳅", "鳝鱼",
            "黑鱼", "鲈鱼", "鳜鱼", "鲑鱼", "鲟鱼", "鲂鱼", "鲴鱼", "鲮鱼", "鲻鱼", "鲷鱼",
            "鲽鱼", "鲆鱼", "鲊鱼", "鲋鱼", "鲌鱼", "鲏鱼", "鲐鱼", "鲒鱼", "鲓鱼", "鲔鱼",
            "鲕鱼", "鲖鱼", "鲗鱼", "鲘鱼", "鲙鱼", "鲚鱼", "鲛鱼", "鲜鱼", "鲝鱼", "鲞鱼",
            "鲟鱼", "鲠鱼", "鲡鱼", "鲢鱼", "鲣鱼", "鲤鱼", "鲥鱼", "鲦鱼", "鲧鱼", "鲨鱼",
            "鲩鱼", "鲪鱼", "鲫鱼", "鲬鱼", "鲭鱼", "鲮鱼", "鲯鱼", "鲰鱼", "鲱鱼", "鲲鱼",
            "鲳鱼", "鲴鱼", "鲵鱼", "鲶鱼", "鲷鱼", "鲸鱼", "鲹鱼", "鲺鱼", "鲻鱼", "鲼鱼",
            "鲽鱼", "鲾鱼", "鲿鱼", "鳀鱼", "鳁鱼", "鳂鱼", "鳃鱼", "鳄鱼", "鳅鱼", "鳆鱼",
            "鳇鱼", "鳈鱼", "鳉鱼", "鳊鱼", "鳋鱼", "鳌鱼", "鳍鱼", "鳎鱼", "鳏鱼", "鳐鱼",
            "鳑鱼", "鳒鱼", "鳓鱼", "鳔鱼", "鳕鱼", "鳖鱼", "鳗鱼", "鳘鱼", "鳙鱼", "鳚鱼",
            "鳛鱼", "鳜鱼", "鳝鱼", "鳞鱼", "鳟鱼", "鳠鱼", "鳡鱼", "鳢鱼", "鳣鱼", "鳤鱼",
            "鳥鱼", "鳦鱼", "鳧鱼", "鳨鱼", "鳩鱼", "鳪鱼", "鳫鱼", "鳬鱼", "鳭鱼", "鳮鱼",
            "鳯鱼", "鳰鱼", "鳱鱼", "鳲鱼", "鳳鱼", "鳴鱼", "鳵鱼", "鳶鱼", "鳷鱼"
        );
    }
}
